Row compression is an interesting feature that was introduced in IBM DB2® brand computer software version 9 (registered mark of International Business Machines Corporation, Armonk, N.Y., USA) (“IBM”). To summarize, DB2® software creates a dictionary of values for each compressed table, and compresses each row, replacing the value by a mapped value in the dictionary. The result is a huge saving, in terms of disk storage requirements, and therefore in total cost of operations/ownership. The IBM DB2 Database for Linux, UNIX, and Windows Information Center, http://publib.boulder.ibm.com/infocenter/db2luw/v9/indexjsp, expressly incorporated herein by reference in its entirety for all purposes, describes row compression. FIG. 1 shows example rows 100. Row 102 lists the name, department, salary, city, state, and postal “zip” code for employee “Fred,” while row 104 lists similar information for employee “John.” FIG. 2 shows uncompressed data storage at 202 and compressed data storage at 204. A dictionary is shown at 206. “Dept 500” is replaced by mapped value 01 while “Plano, Tex. 24355” is replaced by mapped value 02.
As a row needs to be uncompressed before being used by the internal query processor, additional central processing unit (CPU) cost is required to perform any queries on the compressed table. In “Row Compression in DB2 9: Analysis on DSS and OLTP Database Environments,” Y. H Lee, N. Bissoon, and V. Chang, July 2006, available at http://www3.software.ibm.com/ibmdl/pub/software/dw/dm/db2/dm-0610chang/Row_Compression.pdf, expressly incorporated herein by reference in its entirety for all purposes, the authors present comparative results of decision support system (DSS) and on-line transaction processing (OLTP) workload on an uncompressed and compressed database, using standard metrics. Their analysis concludes that even though some queries of the workload show an improvement in execution time (in the compressed case over the uncompressed case), there are other queries for which the execution time increases. This effect is more pronounced in the case of DSS workloads. Therefore, even though the gain in terms of storage saving is clear, the overall performance of a workload in a database using row compression has to be analyzed carefully.
As with most of the major database manager software packages, DB2® software has a value compression mechanism. Value compression provides an alternate method of representing the internal storage format of a data row. The disk storage savings depends on the table column definition. In this situation, NULLs and zero-length data that have been assigned to defined variable-length data types (VARCHAR, VARGRAPHICS, LONG VARCHAR, LONG VARGRAPHIC, BLOB, CLOB, and DBCLOB) will not be stored on disk. Row compression is different from value compression. Row compression does not depend on the table column definition. It replaces common byte patterns in a data row with shorter symbol strings. The storage savings are greater than the savings provided with value compression. DB2® universal data base (UDB) software implements row compression.